package com.yalu.read.excel.order;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.yalu.entity.OrderData;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;

public class OrderExcelRead {
    private static final List<String> CURRENCY_LIST = Arrays.asList("币种","Currency");
    private static final List<String> AMOUNT_LIST = Arrays.asList("销售收入");
    private static final List<String> COMMISSION_LIST = Arrays.asList("佣金");

    /**
     * 测试pdf文件的创建
     *
     * @param args
     */
    public static void main(String[] args) {
        String filePath = "E://2022.0522//";  //这里先手动把绝对路径的文件夹给补上。
        List<String> filePaths = new ArrayList<>();
        recursionFile(filePath, filePaths);
        List<OrderData> list = new ArrayList<>();
        for (String fileName : filePaths) {
            OrderData orderData = OrderExcelRead.readExcel(fileName);
            if (Objects.isNull(orderData)) {
                System.out.println("未处理的文件：" + fileName);
                orderData = new OrderData();
                orderData.setFileName(fileName);
            }
            list.add(orderData);
        }
        System.out.println("文件数量：" + filePaths.size() + " 数据数量: " + list.size());
        DateTimeFormatter dtf = DateTimeFormatter.ofPattern("yyyyMMddHHmmss");
        String outFileName = "D:\\yalu\\订单_" + LocalDateTime.now().format(dtf) + ".xlsx";
        writerFils(list, outFileName);
    }

    /**
     * 读PDF文件，使用了pdfbox开源项目
     *
     * @param fileName
     */
    public static OrderData readExcel(String fileName) {
        File file = new File(fileName);
        FileInputStream in = null;
        InputStream inputStream = null;
        try {
            inputStream = new FileInputStream(fileName);
            List<Object> dataList = EasyExcelFactory.read(inputStream, new Sheet(1, 0));
            if (!CollectionUtils.isEmpty(dataList)) {
                int size = dataList.size();
                int currencyIndex = 0;
                int amountIndex = 0;
                int commissionIndex = 0;
                String date = null;
                String currency = null;
                for (int i = 0; i < size; i++) {
                    List<String> lineList = (List<String>) dataList.get(i);
                    if (i == 0) {
                        for (int y = 0; y < lineList.size(); y++) {
                            String field = lineList.get(y);
                            if (isExistText(field, CURRENCY_LIST)) {
                                currencyIndex = y;
                            }
                            if (isExistText(field, AMOUNT_LIST)) {
                                amountIndex = y;
                            }
                            if (isExistText(field, COMMISSION_LIST)) {
                                commissionIndex = y;
                            }
                            List<String> nextLineList = (List<String>) dataList.get(i + 1);
                            date = fileName.split("\\\\")[2].substring(0, 6);
                            currency = nextLineList.get(currencyIndex);
                        }
                    } else {
                        String currencyNow = lineList.get(currencyIndex);
                        if (!StringUtils.hasText(currencyNow)) {
                            String amount = lineList.size() > amountIndex ? lineList.get(amountIndex) : null;
                            String commission = lineList.size() > commissionIndex ? lineList.get(commissionIndex) : null;
                            OrderData orderData = new OrderData();
                            orderData.setDate(date);
                            orderData.setCurrency(currency);
                            orderData.setAmount(amount);
                            orderData.setCommission(commission);
                            orderData.setFileName(fileName);
                            return orderData;
                        }
                    }
                }
            }
        } catch (Exception e) {
            System.out.println("读取PDF文件" + file.getAbsolutePath() + "生失败！" + e);
            e.printStackTrace();
        } finally {
            if (in != null) {
                try {
                    in.close();
                } catch (IOException e1) {
                }
            }
        }
        return null;
    }

    private static boolean isNull(String str) {
        boolean isNull = false;
        if (StringUtils.hasText(str)) {
            if (!"null".equals(str) && !"[null".equals(str) && !"null]".equals(str)) {
                isNull = true;
            }
        }
        return isNull;
    }

    /**
     * 写入文件
     *
     * @param data
     * @param outFileName
     */
    public static void writerFils(List<OrderData> data, String outFileName) {
        try {
            ExcelWriter excelWriter = EasyExcel.write(outFileName, OrderData.class).build();
            String sheetName = "1";
            WriteSheet writeSheet = EasyExcel.writerSheet(sheetName).build();
            excelWriter.write(data, writeSheet);
            // 千万别忘记finish 会帮忙关闭流
            excelWriter.finish();
        } catch (Exception e) {
            System.out.println("文件写入失败：" + e.getMessage());
        }
    }

    /**
     * 递归文件
     *
     * @param path
     * @param filePaths
     */
    public static void recursionFile(String path, List<String> filePaths) {
        if (StringUtils.hasText(path)) {
            File file = new File(path);
            for (File f : file.listFiles()) {
                if (f.isDirectory()) {
                    recursionFile(f.getPath(), filePaths);
                } else {
                    if (f.getPath().contains("整理") && (f.getPath().toLowerCase().contains(".xls") || f.getPath().toLowerCase().contains(".xlsx"))) {
                        filePaths.add(f.getPath());
                    }
                }
            }
        }
    }

    /**
     * 判断内容是否存在
     *
     * @param content 内容
     * @param list    内容集合
     * @return
     */
    public static boolean isExistText(String content, List<String> list) {
        if (StringUtils.hasText(content)) {
            for (String text : list) {
                if (content.toLowerCase().contains(text.toLowerCase())) {
                    return true;
                }
            }
        }
        return false;
    }
}
